﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Jungle.Code.DAL;
using Jungle.Code.Utils;
using System.Data.SqlClient;
using System.Data;

namespace Jungle.Code.CategoryManager
{
    public class Categories
    {
        public string err;
        private string ConnectionString = DBHelper.ConnectionString;

        public List<Categories> CategoriesList;

        #region Fields
        private int categoryId;
        private string categoryTitle;
        private int parentCategoryId;
        private bool problem;
        private bool visible;
        #endregion

        #region Properties
        public int CategoryId { get { return categoryId; } set { categoryId = value; } }
        public string CategoryTitle { get { return categoryTitle; } set { categoryTitle = value; } }
        public int ParentCategoryId { get { return parentCategoryId; } set { parentCategoryId = value; } }
        public bool Problem { get { return problem; } set { problem = value; } }
        public bool Visible { get { return visible; } set { visible = value; } }
        #endregion

        //------------------------------------------------------------------------
        public void Add()
        {
            SqlConnection connect = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand("catalog.Category_Add", connect);
            command.CommandType = CommandType.StoredProcedure;
            try
            {
                command.Parameters.Add("@Title", SqlDbType.VarChar).Value = CategoryTitle;
                command.Parameters.Add("@ParentCategoryId", SqlDbType.VarChar).Value = ParentCategoryId;
                command.Parameters.Add("@Problem", SqlDbType.Bit).Value = Problem;
                command.Parameters.Add("@Visible", SqlDbType.Bit).Value = Visible;

                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                err = ex.ToString();

            }
            finally
            {
                command.Connection.Close();
            } 
        }
        //------------------------------------------------------------------------       
        public void Update()
        {
            SqlConnection connect = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand("catalog.Category_Update", connect);
            command.CommandType = CommandType.StoredProcedure;
            try
            {
                command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = CategoryId;
                command.Parameters.Add("@Title", SqlDbType.VarChar).Value = CategoryTitle;
                command.Parameters.Add("@ParentCategoryId", SqlDbType.Int).Value = ParentCategoryId;
                command.Parameters.Add("@Problem", SqlDbType.Bit).Value = Problem;
                command.Parameters.Add("@Visible", SqlDbType.Bit).Value = Visible;

                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                err = ex.ToString();

            }
            finally
            {
                command.Connection.Close();
            } 
        }
        //------------------------------------------------------------------------
        public int GetAllCategories()
        {
            int count = 0;
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                CategoriesList = new List<Categories>();
                SqlCommand command = new SqlCommand("catalog.Category_GetAll", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                
                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Categories p = new Categories();
                    p.CategoryId = (int)reader["CategoryId"];
                    p.CategoryTitle = reader["Title"].ToString().Trim();
                    p.ParentCategoryId = (int)reader["ParentCategoryId"];
                    p.Problem = (bool)reader["Problem"];
                    p.Visible = (bool)reader["Visible"];
                    CategoriesList.Add(p);
                    count++;
                }

                reader.Close();
                command.Connection.Close();

            }
            return count;        
        }
        //------------------------------------------------------------------------
        public void GetMainCategories()
        { 
            
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                CategoriesList = new List<Categories>();
                SqlCommand command = new SqlCommand("SELECT * FROM catalog.Categories where [ParentCategoryId] = '0' AND [Visible] = 'True'", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.Text;

                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Categories c = new Categories();
                    c.CategoryId = Convert.ToInt32(reader["CategoryId"]);
                    c.CategoryTitle = reader["Title"].ToString().Trim();
                    c.ParentCategoryId = Convert.ToInt32(reader["ParentCategoryID"]);
                    c.Problem = Convert.ToBoolean(reader["Problem"]);
                    c.Visible = Convert.ToBoolean(reader["Visible"]);

                    CategoriesList.Add(c);
                }
                reader.Close();
                command.Connection.Close();
            }
        }
        //------------------------------------------------------------------------
        public int GetChildCategories()
        {
            int count = 0;
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                CategoriesList = new List<Categories>();
                SqlCommand command = new SqlCommand("catalog.Category_GetChilds", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ParentCategoryId", SqlDbType.Int).Value = CategoryId;

                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Categories p = new Categories();
                    p.CategoryId = (int)reader["CategoryId"];
                    p.CategoryTitle = reader["Title"].ToString().Trim();
                    p.ParentCategoryId = (int)reader["ParentCategoryId"];
                    p.Problem = (bool)reader["Problem"];
                    p.Visible = (bool)reader["Visible"];
                    CategoriesList.Add(p);
                    count++;
                }

                reader.Close();
                command.Connection.Close();

            }
            return count; 
        }
        //------------------------------------------------------------------------
        public string GetAllChilds(int parentId)
        {
            string childs = "";
            int catId = CategoryId;
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("select CategoryId from catalog.Categories where [ParentCategoryId]=" + catId.ToString(), connect);
                SqlDataReader reader = null;

                command.Connection.Open();
                reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        childs += reader["CategoryId"].ToString().Trim() + ",";
                        childs += GetAllChilds((int)reader["CategoryId"]);
                    }
                }
                reader.Close();
                command.Connection.Close();
            }

            return childs;
        }
        //------------------------------------------------------------------------
        public bool GetById()
        {
            try
            {
                using (SqlConnection connect = new SqlConnection(ConnectionString))
                {
                    CategoriesList = new List<Categories>();
                    SqlCommand command = new SqlCommand("catalog.Category_GetById", connect);
                    SqlDataReader reader = null;

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = CategoryId;

                    command.Connection.Open();
                    reader = command.ExecuteReader();

                    if (reader == null) { return false; }

                    while (reader.Read())
                    {
                        CategoryId = (int)reader["CategoryId"];
                        CategoryTitle = reader["Title"].ToString().Trim();
                        ParentCategoryId = (int)reader["ParentCategoryId"];
                        Problem = (bool)reader["Problem"];
                        Visible = (bool)reader["Visible"];
                    }

                    reader.Close();

                    command.Connection.Close();

                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        //------------------------------------------------------------------------
        public bool IsExist(int Id)
        {
            try
            {
                using (SqlConnection connect = new SqlConnection(ConnectionString))
                {
                    CategoriesList = new List<Categories>();
                    SqlCommand command = new SqlCommand("SELECT * FROM catalog.Categories WHERE [CategoryId] = "+Id.ToString().Trim(), connect);
                    SqlDataReader reader = null;

                    command.CommandType = CommandType.Text;
                    
                    command.Connection.Open();
                    reader = command.ExecuteReader();
                    err = reader.HasRows.ToString();
                    if (reader.HasRows) { return true; }

                    reader.Close();

                    command.Connection.Close();

                }
                return false;
            }
            catch
            {
                return false;
            }
        }
        //------------------------------------------------------------------------
        public void Delete()
        {
            SqlConnection connect = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand("catalog.Category_Delete", connect);
            command.CommandType = CommandType.StoredProcedure;
            try
            {
                command.Parameters.Add("@CategoryId", SqlDbType.VarChar).Value = CategoryId;

                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                err = ex.ToString();

            }
            finally
            {
                command.Connection.Close();
            } 
        }
        //------------------------------------------------------------------------
        public static string GetCategoryName(int catId)
        {
            try
            {
                if (catId == 0) { return "Главное меню"; }

                Categories cat = new Categories();
                cat.CategoryId = catId;
                if (cat.GetById())
                {
                    return cat.CategoryTitle;
                }
                else
                {
                    return "удален";
                }
            }
            catch(Exception er)
            {                
                return "none";
            }
        }
        
    }
}
